﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel;
using MySql.Data.MySqlClient;
using System.Configuration;
using System.Data;

namespace DetetiveKGB.Models
{
    public class UserModel
    {
        #region PROPERTIES
        public int LoginId { get; set; }
        public int UserId { get; set; }

        [Required(ErrorMessage="O nome é obrigatório.")]
        [DisplayName("Nome")]
        public string Name { get; set; }

        [Required(ErrorMessage="O login é obrigatório.")]
        [DisplayName("Login")]
        public string UserName { get; set; }

        public string ApplicationName { get; set; }

        [Required(ErrorMessage="O email é obrigatório.")]
        [DataType(DataType.EmailAddress)]
        [DisplayName("Email")]
        public string Email { get; set; }
        
        [DisplayName("Obs.")]
        public string Notes { get; set; }

        public string Password { get; set; }

        public string Question { get; set; }

        public string Answer { get; set; }

        public bool IsAproved { get; set; }

        public DateTime LastActivityDate { get; set; }
        public DateTime LastLoginDate { get; set; }
        public DateTime LastPasswordChange { get; set; }
        public DateTime CreationDate { get; set; }

        [DisplayName("Bloqueado")]
        public bool IsLockedOut { get; set; }
        public DateTime LastLockedOutDate { get; set; }
        public int FailedAttemptCount { get; set; }
        public DateTime FailedAttemptWindow { get; set; }
        public int FailedAttemptAnswerCount { get; set; }
        public DateTime FailedAttemptAnswerWindow { get; set; }
        #endregion

        public int Add()
        {
            int result = -1;
            MySqlConnection cn = new MySqlConnection(ConfigurationManager.ConnectionStrings["MySQL"].ConnectionString);
            MySqlCommand cm = new MySqlCommand(SqlResources.SqlUser.Add, cn);
            try
            {
                cn.Open();
                cm.Parameters.AddWithValue("username", this.UserName);
                cm.Parameters.AddWithValue("email", this.Email);
                cm.Parameters.AddWithValue("notes", this.Notes);
                cm.Parameters.AddWithValue("creationDate", DateTime.Now);
                cm.Parameters.AddWithValue("isLockedOut", this.IsLockedOut);
                result = Convert.ToInt32(cm.ExecuteScalar());
            }
            catch (MySqlException e)
            {
                throw e;
            }
            finally
            {
                cm.Dispose();
                cn.Dispose();
            }
            return result;
        }

        public void AddUser(int loginId)
        {
            MySqlConnection cn = new MySqlConnection(ConfigurationManager.ConnectionStrings["MySQL"].ConnectionString);
            MySqlCommand cm = new MySqlCommand(SqlResources.SqlUser.AddUser, cn);
            try
            {
                cn.Open();
                cm.Parameters.AddWithValue("name", this.Name);
                cm.Parameters.AddWithValue("login", loginId);
                cm.ExecuteNonQuery();
            }
            catch (MySqlException e)
            {
                throw e;
            }
            finally
            {
                cm.Dispose();
                cn.Dispose();
            }
        }

        public void UpdateUser(int loginId)
        {
            MySqlConnection cn = new MySqlConnection(ConfigurationManager.ConnectionStrings["MySQL"].ConnectionString);
            MySqlCommand cm = new MySqlCommand(SqlResources.SqlUser.UpdateUser, cn);
            try
            {
                cn.Open();
                cm.Parameters.AddWithValue("name", this.Name);
                cm.Parameters.AddWithValue("login", loginId);
                cm.ExecuteNonQuery();
            }
            catch (MySqlException e)
            {
                throw e;
            }
            finally
            {
                cm.Dispose();
                cn.Dispose();
            }
        }

        public void Update()
        {
            MySqlConnection cn = new MySqlConnection(ConfigurationManager.ConnectionStrings["MySQL"].ConnectionString);
            MySqlCommand cm = new MySqlCommand(SqlResources.SqlUser.Update, cn);
            try
            {
                cn.Open();
                cm.Parameters.AddWithValue("login", this.LoginId);
                cm.Parameters.AddWithValue("notes", this.Notes);
                cm.Parameters.AddWithValue("isLockedOut", this.IsLockedOut);
                cm.ExecuteScalar();
            }
            catch (MySqlException e)
            {
                throw e;
            }
            finally
            {
                cm.Dispose();
                cn.Dispose();
            }
        }

        public static UserModel Get(int loginId)
        {
            UserModel result = null;
            using (MySqlConnection cn = new MySqlConnection(ConfigurationManager.ConnectionStrings["MySQL"].ConnectionString))
            {
                using (MySqlCommand cmd = new MySqlCommand(SqlResources.SqlUser.Get, cn))
                {
                    try
                    {
                        MySqlDataReader dr = null;
                        cn.Open();
                        cmd.Parameters.AddWithValue("login", loginId);
                        dr = cmd.ExecuteReader(CommandBehavior.SingleRow);

                        if (dr.Read())
                        {
                            result = new UserModel()
                            {
                                LoginId                  = dr.GetInt32(0),
                                UserId                   = !dr.IsDBNull(1) ? dr.GetInt32(1) : 0,
                                Name                     = !dr.IsDBNull(2) ? dr.GetString(2) : string.Empty,
                                UserName                 = dr.GetString(3),
                                ApplicationName          = dr.GetString(4),
                                Email                    = dr.GetString(5),
                                Notes                    = dr.IsDBNull(6) ? string.Empty : dr.GetString(6),
                                Password                 = dr.GetString(7),
                                Question                 = dr.GetString(8),
                                Answer                   = dr.GetString(9),
                                CreationDate             = dr.GetDateTime(10),
                                IsLockedOut              = dr.GetBoolean(11),
                                FailedAttemptCount       = dr.GetInt32(12),
                                FailedAttemptAnswerCount = dr.GetInt32(13)
                            };
                            result.UserId = dr.IsDBNull(1) ? 0 : dr.GetInt32(1);
                        }
                    }
                    catch (MySqlException ex)
                    {
                        throw ex;
                    }
                }
            }
            return result;
        }

        public static List<UserModel> ListAll()
        {
            List<UserModel> result = new List<UserModel>();
            MySqlConnection cn = new MySqlConnection(ConfigurationManager.ConnectionStrings["MySQL"].ConnectionString);
            MySqlCommand cm = new MySqlCommand(SqlResources.SqlUser.List, cn);
            MySqlDataReader dr = null;
            try
            {
                cn.Open();
                dr = cm.ExecuteReader();

                while (dr.Read())
                {
                    UserModel am = new UserModel()
                    {
                        LoginId     = dr.GetInt32(0),
                        UserName    = dr.GetString(1),
                        UserId      = dr.IsDBNull(2) ? 0 : dr.GetInt32(2),
                        Name        = dr.IsDBNull(3) ? string.Empty : dr.GetString(3),
                        Email       = dr.GetString(4),
                        Notes       = dr.IsDBNull(5) ? string.Empty : dr.GetString(5),
                        IsLockedOut = dr.GetBoolean(6)
                    };
                    result.Add(am);
                }
            }
            catch (MySqlException e)
            {
                throw e;
            }
            finally
            {
                dr.Close();
                cm.Dispose();
                cn.Dispose();
            }
            return result;
        }
    }
}